# 项目信息：projectInfo {projectName : "", projectDepartment : "", projectUnit : "", projectManagement : ""}
# 人员信息：staffInfo {staffId : "", staffName : "", staffUnit : "", staffDepartment : "", staffPost : "", staffLevel : ""}
# 项目工时信息：projectWorkTime {"$projectName" : {"$yearMonth" : {"$staffName" : "$workTime"} } }
# 人员工时信息：staffWorkTime {"$staffName" : {"$yearMonth" : { "$projectName" : "$workTime"}}}

import openpyxl
import os
import re

myDept = '多金系统开发部'

# 获取目录里的文件
def DataInput(dir):
    projectWorkTime = {}
    projectNames = {}
    for folderName, subfolders, filenames in os.walk(dir):
        for filename in filenames:
            name = os.path.basename(filename).split('.')[0]
            if os.path.basename(filename).split('.')[1] == 'xlsx':
                wb = openpyxl.load_workbook(dir + "/" + filename)
                worksheet = wb[name]
                readWorkTimeBySingleFile(worksheet, name, projectWorkTime)
                readProjectNames(worksheet, filename, projectNames)
    #print(projectWorkTime)
    #print(projectNames)
    myProjects = getProjectNamesByDept(myDept, projectNames)
    getMyProjectWorkTime(myProjects, projectWorkTime)

# 输出结果
#def DataOputput():
#    writeProjectNames(projectNames)

# 获取指定项目的工时
def getMyProjectWorkTime(myProjectNames, projectWorkTime):
    myProjectWorkTime = {}
    for k in projectWorkTime.keys():
        if k in myProjectNames:
            myProjectWorkTime[k] = projectWorkTime.get(k)
    #print(myProjectWorkTime)
    return myProjectWorkTime

# 获取文件名的年月
def getYearMonth(filename):
    yearMonthRegex = re.compile(r'\d{4}年\d{2}月')
    mo = yearMonthRegex.search(filename)
    return mo.group()

# 获取文件名的内外包
def getFileType(filename):
    fileType = re.compile(r'_\w{2}')
    mo = fileType.search(filename)
    return mo.group()

# 读取工时
def readWorkTimeBySingleFile(worksheet, filename, projectWorkTime):
    yearMonth = getYearMonth(filename)
    inout = getFileType(filename)
    # 外包
    if inout == '_外勤':
        projectNameIndex = 11
        staffNameIndex = 1
        workTimeIndex = 14
    else:
        # 内勤
        projectNameIndex = 5
        staffNameIndex = 1
        workTimeIndex = 10
    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
        projectName = row[projectNameIndex].value
        staffName = row[staffNameIndex].value
        workTime = row[workTimeIndex].value
        if projectName not in projectWorkTime.keys():
            projectWorkTime[projectName] = {yearMonth: {staffName: workTime}}
        else:
            if yearMonth not in projectWorkTime[projectName].keys():
                projectWorkTime[projectName][yearMonth] = {staffName: workTime}
            else:
                if staffName not in projectWorkTime[projectName][yearMonth].keys():
                    projectWorkTime[projectName][yearMonth][staffName] = workTime
                else:
                    projectWorkTime[projectName][yearMonth][staffName] += workTime

# 读取项目列表
def readProjectNames(worksheet, filename, projectNames):
    inout = getFileType(filename)
    # 外包
    if inout == '_外勤':
        projectNameIndex = 11
        projectDeptIndex = 10
    else:
        # 内勤
        projectNameIndex = 5
        projectDeptIndex = 7
    for row in worksheet.iter_rows(min_row=5, max_col=worksheet.max_column, max_row=worksheet.max_row):
        projectName = row[projectNameIndex].value
        projectDept = row[projectDeptIndex].value
        if projectName not in projectNames.keys():
            projectNames[projectName] = {'dept': projectDept}

# 获取指定部门的项目
def getProjectNamesByDept(myDept, projectNames):
    theProjectNames = []
    for k in projectNames.keys():
        dept = projectNames[k]['dept']
        if dept == myDept:
            theProjectNames.append(k)
    print(theProjectNames)
    return theProjectNames

if __name__ == '__main__':
    DataInput("/Users/zhangxiangbin/awesome-doc/02 招商金科/工时统计/2022年/")
